var express = require('express');
var router = express.Router();
const db = require('../db/db');
const path = require("path");
const fs = require("fs");
let multer=require('multer')
let upload = multer({ dest: './public/uploads/file' })

router.use(upload.any());

router.post('/', function (req, res, next) {
    let {page, pageSize} = req.body;
    let start = (page - 1) * pageSize;
    let no = req.session.no
    let sql = `select p.id,
                      p.name,
                      team_name,
                      s.name                                      as leader_name,
                      p.meaning,
                      step,
                      i.path,
                      (select count(*)
                       from projects
                       WHERE team_id IN (SELECT id
                                         FROM team
                                         WHERE leader = ${no}
                                            OR member_1 = ${no}
                                            OR member_2 = ${no}
                                            OR member_3 = ${no})) as total
               from projects p,
                    team t,
                    students s,
                    item i
               WHERE team_id IN (SELECT id
                                 FROM team
                                 WHERE leader = ${no}
                                    OR member_1 = ${no}
                                    OR member_2 = ${no}
                                    OR member_3 = ${no})
                 AND t.id = team_id
                 and s.\`No\` = leader
                 and i.id = p.item_id
               ORDER BY p.id
               limit ${start},${pageSize}`
    db(sql, function (err, rows) {
        if (err) {
            res.render('error.html', {title: 'Express', datas: []});
        } else {
            res.json({datas: rows, total: rows[0]['total']});
        }
    })
});

router.post('/declared', function (req, res, next) {
    let {page, pageSize} = req.body;
    let start = (page - 1) * pageSize;
    let no = req.session.no
    let sql = `select p.id,
                      p.name,
                      team_name,
                      s.name                                      as leader_name,
                      p.meaning,
                      step,
                      i.path,
                      (select count(*)
                       from projects
                       WHERE team_id IN (SELECT id
                                         FROM team
                                         WHERE leader = ${no}
                                            OR member_1 = ${no}
                                            OR member_2 = ${no}
                                            OR member_3 = ${no}) and ISNULL(file) and state = 1) as total
               from projects p,
                    team t,
                    students s,
                    item i
               WHERE team_id IN (SELECT id
                                 FROM team
                                 WHERE leader = ${no}
                                    OR member_1 = ${no}
                                    OR member_2 = ${no}
                                    OR member_3 = ${no})
                 AND t.id = team_id
                 and s.\`No\` = leader
                 and i.id = p.item_id
                 and ISNULL(p.file)
                 and state = 1
               ORDER BY p.id
               limit ${start},${pageSize}`
    db(sql, function (err, rows) {
        if (err) {
            res.render('error.html', {title: 'Express', datas: []});
        } else {
            res.json({datas: rows, total: rows[0]['total']});
        }
    })
});

router.post('/submitted', function (req, res, next) {
    let {page, pageSize} = req.body;
    let start = (page - 1) * pageSize;
    let no = req.session.no
    let sql = `select p.id,
                      p.name,
                      team_name,
                      s.name                                      as leader_name,
                      p.meaning,
                      step,
                      i.path,
                      file,
                      (select count(*)
                       from projects
                       WHERE team_id IN (SELECT id
                                         FROM team
                                         WHERE leader = ${no}
                                            OR member_1 = ${no}
                                            OR member_2 = ${no}
                                            OR member_3 = ${no}) and !ISNULL(file) and state = 1) as total
               from projects p,
                    team t,
                    students s,
                    item i
               WHERE team_id IN (SELECT id
                                 FROM team
                                 WHERE leader = ${no}
                                    OR member_1 = ${no}
                                    OR member_2 = ${no}
                                    OR member_3 = ${no})
                 AND t.id = team_id
                 and s.\`No\` = leader
                 and i.id = p.id
                 and !ISNULL(p.file)
                 and state = 1
               ORDER BY p.id
               limit ${start},${pageSize}`
    db(sql, function (err, rows) {
        if (err) {
            res.render('error.html', {title: 'Express', datas: []});
        } else {
            res.json({datas: rows, total: rows[0]['total']});
        }
    })
});

router.post('/message', function (req, res, next) {
    let {page, pageSize} = req.body;
    let start = (page - 1) * pageSize;
    let no = req.session.no
    let sql = `SELECT m.id,content,r.role,p.name,
            (SELECT count(*) FROM message m JOIN projects p ON p.id = m.pro_id WHERE p.team_id IN (SELECT id FROM team WHERE leader = ${no} OR member_1 = ${no} OR member_2 = ${no} OR member_3 = ${no})) as total
            FROM message m JOIN projects p ON p.id = m.pro_id JOIN role r ON r.id = m.role_id 
            WHERE p.team_id IN (SELECT id FROM team WHERE leader = ${no} OR member_1 = ${no} OR member_2 = ${no} OR member_3 = ${no}) ORDER BY m.id DESC limit ${start},${pageSize}`
    db(sql, function (err, rows) {
        if (err) {
            res.render('error.html', {title: 'Express', datas: []});
        } else {
            res.json({datas: rows, total: rows[0]['total']});
        }
    })
});

router.post('/addMessage', function (req, res) {
    let {id,content} = req.body;
    let role= req.session.role
    let sql = `insert into message(content,pro_id,role_id) value (${content},${id},${role})`
    db(sql, function (err, rows) {
        if (err) {
            res.send({flag: false, msg: "提交建议失败"});
            throw err;
        } else {
            res.send({flag: true, msg: "提交建议成功"});
        }
    })
});

router.post('/getValue', function (req, res) {
    let no = req.session.no
    let sql = `SELECT count(*) as v FROM message m JOIN projects p ON p.id = m.pro_id 
                WHERE p.team_id IN (SELECT id FROM team WHERE leader = ${no} OR member_1 = ${no} OR member_2 = ${no} OR member_3 = ${no}) and reader not like '%${no}%'`
    db(sql, function (err, rows) {
        if (err) {
            console.log("获取失败")
            throw err;
        } else {
            res.send({v: rows[0]['v']});
        }
    })
});

router.post('/clear', function (req, res) {
    let no = req.session.no
    let sql = `UPDATE message m JOIN projects p ON p.id = m.pro_id SET reader = CONCAT(reader,'${no},')  WHERE p.team_id IN (SELECT id FROM team WHERE leader = ${no} OR member_1 = ${no} OR member_2 = ${no} OR member_3 = ${no})`
    db(sql, function (err, rows) {
        if (err) {
            console.log("清除失败")
            throw err;
        } else {
            console.log("清除成功")
            res.send({v: ''});
        }
    })
});

router.post('/addPro', function (req, res) {
    let {name, meaning, question, innovations, plan, gain,item_id,major_id} = req.body;
    let sql = "insert into projects(name,meaning,question,innovations,plan,gain,item_id,team_id,major_id) values('" + name + "','" + meaning + "','" + question + "','" + innovations + "','" + plan + "','" + gain + "','" + +item_id + "'," + "(SELECT max(id) FROM team)," + major_id + ")"
    db(sql, function (err, rows) {
        if (err) {
            res.send({flag: false, msg: "项目申报失败"});
            throw err;
        } else {
            res.send({flag: true, msg: "项目申报成功"});
        }
    })
});


router.post('/addTeam', function (req, res) {
    let {team_name, leader, member_1, member_2,teacher_name} = req.body;
    let sql = "insert into team(team_name,leader,member_1,member_2,instructor) values('" + team_name + "','" + leader + "','" + member_1 + "','" + member_2 + "',(select id from teachers where name = '" + teacher_name +  "'))"
    db(sql, function (err, rows) {
        if (err) {
            res.send({flag: false, msg: "项目申报失败"});
            throw err;
        } else {
            res.send({flag: true, msg: "项目申报成功"});
        }
    })
});

router.post('/check', function (req, res) {
    let {page, pageSize} = req.body;
    let start = (page - 1) * pageSize;
    let tid = req.session.tid
    let role= req.session.role
    let mid = req.session.mid
    let sql = ''
    if (role > 1)
     sql = `SELECT p.id,p.name,team_name as team,team_id,p.meaning,m.\`name\` as major,major_id,file,path,
         (SELECT COUNT(*) From projects p,team t,item i,major m WHERE item_id in (SELECT id FROM item WHERE t_id = ${tid}) AND p.state = 0 AND t.id = p.team_id AND p.item_id = i.id  AND p.major_id = m.id OR team_id in (SELECT id FROM team WHERE instructor = ${tid}) AND p.state = 0 AND t.id = p.team_id AND p.item_id = i.id  AND p.major_id = m.id OR major_id = ${mid} AND p.step = ${role}-1 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id) as total
            FROM projects p,team t,item i,major m WHERE item_id in (SELECT id FROM item WHERE t_id = ${tid}) AND state = 0 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id OR team_id in (SELECT id FROM team WHERE instructor = ${tid}) AND state = 0 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id OR major_id = ${mid} AND step = ${role}-1 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id limit ${start},${pageSize}`
    else
        sql = `SELECT p.id,p.name,team_name as team,team_id,p.meaning,m.\`name\` as major,major_id,file,path,
            (SELECT COUNT(*) From projects p,team t,item i,major m WHERE item_id in (SELECT id FROM item WHERE t_id = ${tid}) AND p.state = 0 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id OR team_id in (SELECT id FROM team WHERE instructor = ${tid}) AND p.state = 0 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id) as total
            FROM projects p,team t,item i,major m WHERE item_id in (SELECT id FROM item WHERE t_id = ${tid}) AND state = 0 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id OR team_id in (SELECT id FROM team WHERE instructor = ${tid}) AND state = 0 AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id limit ${start},${pageSize}`
    db(sql, function (err, rows) {
        if (err) {
            res.send({flag: false});
            throw err;
        } else {
            res.send({flag: true,datas: rows, total: rows[0]['total']});
        }
    })
});

router.post('/checked', function (req, res) {
    let {page, pageSize} = req.body;
    let start = (page - 1) * pageSize;
    let tid = req.session.tid;
    let sql = `SELECT p.id,p.name,team_name as team,p.meaning,m.\`name\` as major,major_id,file,path,if(state,'通过','不通过') as result,
            (SELECT COUNT(*) From projects p,team t,item i,major m WHERE reviewer like '%${tid}%' AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id) as total 
            From projects p,team t,item i,major m WHERE reviewer like '%${tid}%' AND t.id = p.team_id AND p.item_id = i.id AND p.major_id = m.id limit ${start},${pageSize}`
    db(sql, function (err, rows) {
        if (err) {
            res.send({flag: false});
            throw err;
        } else {
            res.send({flag: true,datas: rows,total: rows[0]['total']});
        }
    })
});

router.post('/examine', function (req, res) {
    let {team_id,id,result} = req.body;
    let tid = req.session.tid
    let sql =``
    if (result === 'yes'){
        sql = `select instructor from team where id = ${team_id}`
        db(sql, function (err, rows) {
            if (err) {
                throw err;
            } else {
                if (rows[0].instructor == tid){
                    sql = `update projects set reviewer = CONCAT(reviewer,'${tid},'),step = step+1,state = 1 where id = ${id}`
                }else {
                    sql = `update projects set reviewer = CONCAT(reviewer,'${tid},'),state = 1 where id = ${id}`
                }
                db(sql, function (err, rows) {
                    if (err) {
                        res.send({flag: false, msg: "审核失败"});
                        throw err;
                    } else {
                        res.send({flag: false, msg: "审核成功"});
                    }
                })
            }
        })
    }else {
        sql = `update projects set reviewer = reviewer+'${tid}',state = 0 where id = ${id}`
        db(sql, function (err, rows) {
            if (err) {
                res.send({flag: false, msg: "审核失败"});
                throw err;
            } else {
                res.send({flag: false, msg: "审核成功"});
            }
        })
    }
});

router.post('/upload/:id', (req, res) => {
    let id=req.params.id;
    let filename = new Date().getTime();
    let new_path =  'public\\uploads\\file\\' + filename+ path.parse(req.files[0].originalname).ext;
    let file = '/uploads/file/' + filename+ path.parse(req.files[0].originalname).ext
    fs.rename(req.files[0].path, new_path, (err) => {
        if (err) {
            res.send('失败' + err)
        } else {
            let sql = `update projects set file = '${file}' where id = ${id}`
            db(sql, function (err, rows) {
                if (err) {
                    console.log("提交失败");
                    throw err;
                } else {
                    console.log("提交成功");
                    // window.location.href='http://localhost:8080/submitted'
                }
            })
        }
    })
})

router.post('/analysis', function (req, res) {
    let no = req.session.no
    let sql = `select count(*) as \`value\`, '通过' as name
               from projects
               WHERE team_id IN (SELECT id
                                 FROM team
                                 WHERE leader = ${no}
                                    OR member_1 = ${no}
                                    OR member_2 = ${no}
                                    OR member_3 = ${no})
                 AND state = 1
               UNION
               select count(*) as \`value\`, '不通过' as name
               from projects
               WHERE team_id IN (SELECT id
                                 FROM team
                                 WHERE leader = ${no}
                                    OR member_1 = ${no}
                                    OR member_2 = ${no}
                                    OR member_3 = ${no})
                 AND state = 0`
    db(sql, function (err, rows) {
        if (err) {
            res.send({flag: false});
            throw err;
        } else {
            res.send({flag: true,datas: rows});
        }
    })
});

router.post('/all', function (req, res) {
    let sql = `select count(*) as \`value\`,'软件工程系' as name from projects WHERE major_id = 5
            UNION
            select count(*) as \`value\`,'网络技术系' as name from projects WHERE major_id = 6
            UNION
            select count(*) as \`value\`,'游戏系' as name from projects WHERE major_id = 7
            UNION
            select count(*) as \`value\`,'计算机系' as name from projects WHERE major_id = 8
            UNION
            select count(*) as \`value\`,'数码系' as name from projects WHERE major_id = 9
            UNION
            select count(*) as \`value\`,'电子系' as name from projects WHERE major_id = 10
            UNION
            select count(*) as \`value\`,'外语系' as name from projects WHERE major_id = 11
            UNION
            select count(*) as \`value\`,'管理系' as name from projects WHERE major_id = 12
            UNION
            select count(*) as \`value\`,'财经系' as name from projects WHERE major_id = 13`
    db(sql, function (err, rows) {
        if (err) {
            res.send({flag: false});
            throw err;
        } else {
            res.send({flag: true,datas: rows});
        }
    })
});

module.exports = router;